4  Subqueries

4.1 Reference

Default database in this chapter is “ap”.

Other databases included in the simulation MySQL server:

• om
• ex

4.2 Subqueries

What

Sometimes we want to express complex logic as part of the main query and wish that we could just write another standalone query for that logic.

Example: in WHERE or HAVING clause, we want to match/exclude only a specific set of values from another table that involves more complex subsetting in its own.

Why

• More clear description of what complex logic taking place outside of main query.
• More possibilities and flexibility to query data within one main query.

Example

The following subquery (getting vendor_id from vendors table where vendor_state is California) allows us subset invoices table based on vendor criteria in vendors table.

As you can imagine, if we need more complicated logic to subset for vendor_id, we can specify more in WHERE clause in the subquery. - much more expressive and telling how we select vendor_ids - more flexible if we need to add more logic/other operations in vendors table

SELECT invoice_number, invoice_date, invoice_total
FROM invoices
WHERE vendor_id IN
    (SELECT vendor_id
     FROM vendors
     WHERE vendor_state = 'CA')
ORDER BY invoice_date
LIMIT 15

Displaying records 1 - 15

invoice_number	invoice_date	invoice_total
125520-1	2022-04-24	95.00
97/488	2022-04-24	601.95
111-92R-10096	2022-04-30	16.33
25022117	2022-05-01	6.00
P02-88D77S7	2022-05-03	856.92
QP58872	2022-05-07	116.54
24863706	2022-05-10	6.00
10843	2022-05-11	4901.26
77290	2022-05-13	1750.00
121897	2022-05-19	450.00
P02-3772	2022-05-21	7125.34
97/486	2022-05-21	953.10
94007005	2022-05-23	220.00
RTR-72-3662-X	2022-05-25	1600.00
97/465	2022-05-25	565.15

4.3 Use Cases

Compare with an Aggregated Result

Here we want to retrieve all invoice records:

• with balance due > 0
• with balance_due below average of outstanding balance due

First, let’s see what average balance due looks like:

SELECT AVG(invoice_total - payment_total - credit_total)
FROM invoices
WHERE invoice_total - payment_total - credit_total > 0

1 records

AVG(invoice_total - payment_total - credit_total)
2910.947

Second, let’s see what invoices balance due look like:

SELECT invoice_number, invoice_date, 
    invoice_total - payment_total - credit_total AS balance_due
FROM invoices
ORDER BY balance_due DESC
LIMIT 15

Displaying records 1 - 15

invoice_number	invoice_date	balance_due
P-0608	2022-07-23	19351.18
0-2436	2022-07-31	10976.06
31361833	2022-07-21	579.42
9982771	2022-07-24	503.20
547480102	2022-08-01	224.00
134116	2022-07-28	90.36
39104	2022-07-10	85.31
263253270	2022-07-22	67.92
263253268	2022-07-21	59.97
963253264	2022-07-18	52.25
263253273	2022-07-22	30.75
963253249	2022-08-02	0.00
963253251	2022-04-16	0.00
963253261	2022-04-16	0.00
I77271-O01	2022-04-26	0.00

Third, let’s see what invoices below average balance due look like:

SELECT invoice_number, invoice_date, 
    invoice_total - payment_total - credit_total AS balance_due
FROM invoices
WHERE invoice_total - payment_total - credit_total <
    (
     SELECT AVG(invoice_total - payment_total - credit_total)
     FROM invoices
     WHERE invoice_total - payment_total - credit_total > 0
    )
ORDER BY balance_due DESC

Displaying records 1 - 15

invoice_number	invoice_date	balance_due
31361833	2022-07-21	579.42
9982771	2022-07-24	503.20
547480102	2022-08-01	224.00
134116	2022-07-28	90.36
39104	2022-07-10	85.31
263253270	2022-07-22	67.92
263253268	2022-07-21	59.97
963253264	2022-07-18	52.25
263253273	2022-07-22	30.75
989319-457	2022-04-08	0.00
263253241	2022-04-10	0.00
963253234	2022-04-13	0.00
2-000-2993	2022-04-16	0.00
963253251	2022-04-16	0.00
963253261	2022-04-16	0.00

Lastly, filter out all of the invoices without any balance due:

SELECT invoice_number, invoice_date, 
    invoice_total - payment_total - credit_total AS balance_due
FROM invoices
WHERE invoice_total - payment_total - credit_total <
    (
     SELECT AVG(invoice_total - payment_total - credit_total)
     FROM invoices
     WHERE invoice_total - payment_total - credit_total > 0
    ) AND invoice_total - payment_total - credit_total > 0
ORDER BY balance_due DESC

9 records

invoice_number	invoice_date	balance_due
31361833	2022-07-21	579.42
9982771	2022-07-24	503.20
547480102	2022-08-01	224.00
134116	2022-07-28	90.36
39104	2022-07-10	85.31
263253270	2022-07-22	67.92
263253268	2022-07-21	59.97
963253264	2022-07-18	52.25
263253273	2022-07-22	30.75

Common Table Expressions (CTE)

Introduction

CTE allows you to assign nicknames for the output of your subqueries and then use the nicknames to go on to finish the main query.

Example, we want to write a query to:

• get a vendor from each state
• this vendor has the highest total invoice amount in that state
• list all states with highest vendor invoice total in that state, and its vendor name

First, let’s get all vendors and their total invoices together:

SELECT vendor_state, 
    vendor_name,
    invoice_total
FROM vendors v INNER JOIN invoices i 
    ON v.vendor_id = i.vendor_id

Displaying records 1 - 15

vendor_state	vendor_name	invoice_total
NV	United Parcel Service	3813.33
TN	Federal Express Corporation	40.20
TN	Federal Express Corporation	138.75
TN	Federal Express Corporation	144.70
TN	Federal Express Corporation	15.50
TN	Federal Express Corporation	42.75
TN	Federal Express Corporation	172.50
CA	Evans Executone Inc	95.00
CA	Zylka Design	601.95
TN	Federal Express Corporation	42.67
TN	Federal Express Corporation	42.50
AZ	Wells Fargo Bank	662.00
CA	Pacific Bell	16.33
CA	Roadway Package System, Inc	6.00
CA	Fresno County Tax Collector	856.92

Second, let’s get total invoice amount by vendors and states

SELECT vendor_state, 
    vendor_name,
    SUM(invoice_total) AS sum_of_invoices
FROM vendors v INNER JOIN invoices i 
    ON v.vendor_id = i.vendor_id
GROUP BY vendor_state, vendor_name

Displaying records 1 - 15

vendor_state	vendor_name	sum_of_invoices
NV	United Parcel Service	23177.96
TN	Federal Express Corporation	4378.02
CA	Evans Executone Inc	95.00
CA	Zylka Design	6940.25
AZ	Wells Fargo Bank	662.00
CA	Pacific Bell	171.01
CA	Roadway Package System, Inc	43.67
CA	Fresno County Tax Collector	856.92
OH	Compuserve	19.90
CA	IBM	1200.12
CA	Yesmed, Inc	4901.26
CA	Pollstar	1750.00
OH	Edward Data Services	207.78
CA	Gostanian General Building	450.00
CA	Digital Dreamworks	7125.34

Third, now, we need to create a nickname for this result so that we can base on the result of that query to select the max total invoice amount by state:

WITH summary AS

(SELECT vendor_state, 
    vendor_name,
    SUM(invoice_total) AS sum_of_invoices
FROM vendors v INNER JOIN invoices i 
    ON v.vendor_id = i.vendor_id
GROUP BY vendor_state, vendor_name)

SELECT vendor_state,
    MAX(sum_of_invoices) AS sum_of_invoices
FROM summary
GROUP BY vendor_state 

10 records

vendor_state	sum_of_invoices
NV	23177.96
TN	4378.02
CA	7125.34
AZ	662.00
OH	207.78
MI	119892.41
TX	2154.42
MA	1367.50
PA	265.36
DC	600.00

Lastly, we create nicknames for both results (yes, you can create CTE for any query that you wish to assign) and write the final query to get selected vendor names to each state and sum_of_invoices. (hnit: use inner join)

WITH summary AS
(
SELECT vendor_state, 
    vendor_name,
    SUM(invoice_total) AS sum_of_invoices
FROM vendors v INNER JOIN invoices i 
    ON v.vendor_id = i.vendor_id
GROUP BY vendor_state, vendor_name
),
top_in_state AS
(
SELECT vendor_state,
    MAX(sum_of_invoices) AS sum_of_invoices
FROM summary
GROUP BY vendor_state
)

SELECT summary.vendor_state, summary.vendor_name,
       top_in_state.sum_of_invoices
FROM summary INNER JOIN top_in_state
    ON summary.vendor_state = top_in_state.vendor_state AND
       summary.sum_of_invoices = top_in_state.sum_of_invoices
ORDER BY summary.vendor_state

10 records

vendor_state	vendor_name	sum_of_invoices
AZ	Wells Fargo Bank	662.00
CA	Digital Dreamworks	7125.34
DC	Reiter’s Scientific & Pro Books	600.00
MA	Dean Witter Reynolds	1367.50
MI	Malloy Lithographing Inc	119892.41
NV	United Parcel Service	23177.96
OH	Edward Data Services	207.78
PA	Cardinal Business Media, Inc.	265.36
TN	Federal Express Corporation	4378.02
TX	Ingram	2154.42

Recursive

Recursive querying can loop through a result set and perform processing to return a final result set. This is similar to “while loop” in other programming languages such as R, Python.

This is commonly used when you want to build a dataset reflecting on hierarchy: one parent record has many child records, each child record can have their own child records. For example, see the employees table where there are multiple managing levels embedded.

SELECT *
FROM ex.employees

9 records

employee_id	last_name	first_name	department_number	manager_id
1	Smith	Cindy	2	NA
2	Jones	Elmer	4	1
3	Simonian	Ralph	2	2
4	Hernandez	Olivia	1	9
5	Aaronsen	Robert	2	4
6	Watson	Denise	6	8
7	Hardy	Thomas	5	2
8	O’Leary	Rhea	4	9
9	Locario	Paulo	6	1

Example: we will build an employee dataset with ranking order. Each employee has the same staff level will have the same ranking.

First, we start at the top of the hierachy.

SELECT employee_id, 
    CONCAT(first_name, ' ', last_name) AS employee_name,
    1 AS ranking
FROM ex.employees
WHERE manager_id IS NULL

1 records

employee_id	employee_name	ranking
1	Cindy Smith	1

Second, we experiment with the next level where staff are directly managed by employee_id 1. At this stage, they should have ranking increased by 1, as they are level 2 staff.

WITH employees_cte AS
(
SELECT employee_id, 
    CONCAT(first_name, ' ', last_name) AS employee_name,
    1 AS ranking
FROM ex.employees
WHERE manager_id IS NULL
)

SELECT employees.employee_id, 
    CONCAT(employees.first_name, ' ', employees.last_name) AS employee_name,
    1+1 AS ranking
FROM ex.employees INNER JOIN employees_cte ON employees.manager_id = employees_cte.employee_id
WHERE employees.employee_id = 2

1 records

employee_id	employee_name	ranking
2	Elmer Jones	2

Notice that if employees have the same level of manager at this point, it will all be returned with the same level of ranking. This is ensured by the “inner join” to employees table.

WITH employees_cte AS
(
SELECT employee_id, 
    CONCAT(first_name, ' ', last_name) AS employee_name,
    1 AS ranking
FROM ex.employees
WHERE manager_id IS NULL
)

SELECT employees.employee_id, 
    CONCAT(employees.first_name, ' ', employees.last_name) AS employee_name,
    1+1 AS ranking
FROM ex.employees INNER JOIN employees_cte ON employees.manager_id = employees_cte.employee_id
WHERE employees.employee_id = 9

1 records

employee_id	employee_name	ranking
9	Paulo Locario	2

If the employee doesn’t have the same management at this time in the employees_cte expression, the result will be empty.

WITH employees_cte AS
(
SELECT employee_id, 
    CONCAT(first_name, ' ', last_name) AS employee_name,
    1 AS ranking
FROM ex.employees
WHERE manager_id IS NULL
)

SELECT employees.employee_id, 
    CONCAT(employees.first_name, ' ', employees.last_name) AS employee_name,
    1+1 AS ranking
FROM ex.employees INNER JOIN employees_cte ON employees.manager_id = employees_cte.employee_id
WHERE employees.employee_id = 8

0 records

employee_id	employee_name	ranking

Third, imagine the following is happening:

1. We are going through this exercise second time, add all of the second level of staff with ranking 2 and append to employees_cte;
2. We are going through this exercise third time and add all of the third level of staff with ranking 3 and append to employees_cte;
3. We are not stopping until we have every record remaining all return empty (i.e. nothing to further append to employees_cte).

Now, we have the following SQL statement to manifest that logic above.

With “UNION ALL”, we need to make sure your recursive query should have exactly the same columns and column names with the initial query.

WITH RECURSIVE employees_cte AS
(
-- Nonrecursive query
SELECT employee_id, 
    CONCAT(first_name, ' ', last_name) AS employee_name,
    1 AS ranking
FROM ex.employees
WHERE manager_id IS NULL

-- this to make sure the "append" action is taking place after each loop
UNION ALL 

-- Recursive query
SELECT employees.employee_id, 
    CONCAT(first_name, ' ', last_name), 
    ranking + 1
FROM ex.employees
    INNER JOIN employees_cte
    ON employees.manager_id = employees_cte.employee_id
)

SELECT *
FROM employees_cte
ORDER BY ranking, employee_id

9 records

employee_id	employee_name	ranking
1	Cindy Smith	1
2	Elmer Jones	2
9	Paulo Locario	2
3	Ralph Simonian	3
4	Olivia Hernandez	3
7	Thomas Hardy	3
8	Rhea O’Leary	3
5	Robert Aaronsen	4
6	Denise Watson	4

Use Subquery in FROM

Here, we want a query that:

• Gets the names of all vendors.
• Finds the latest invoice date for each vendor.
• Lists the vendors with the most recent invoices at the top.

First, let’s see total invoice amounts by vendor name and invoice date

SELECT vendor_name, invoice_date
FROM invoices i LEFT JOIN vendors v ON i.vendor_id = v.vendor_id

Displaying records 1 - 15

vendor_name	invoice_date
United Parcel Service	2022-04-08
Federal Express Corporation	2022-04-10
Federal Express Corporation	2022-04-13
Federal Express Corporation	2022-04-16
Federal Express Corporation	2022-04-16
Federal Express Corporation	2022-04-16
Federal Express Corporation	2022-04-21
Evans Executone Inc	2022-04-24
Zylka Design	2022-04-24
Federal Express Corporation	2022-04-24
Federal Express Corporation	2022-04-25
Wells Fargo Bank	2022-04-26
Pacific Bell	2022-04-30
Roadway Package System, Inc	2022-05-01
Fresno County Tax Collector	2022-05-03

Second, let’s see the latest invoice date by each vendor

SELECT vendor_name, MAX(invoice_date) AS latest_inv
FROM 
( SELECT vendor_name, invoice_date
  FROM invoices i LEFT JOIN vendors v ON i.vendor_id = v.vendor_id ) t
GROUP BY vendor_name
ORDER BY latest_inv DESC

Displaying records 1 - 15

vendor_name	latest_inv
Federal Express Corporation	2022-08-02
Blue Cross	2022-08-01
Malloy Lithographing Inc	2022-07-31
Cardinal Business Media, Inc.	2022-07-28
Zylka Design	2022-07-25
United Parcel Service	2022-07-24
Ford Motor Credit Company	2022-07-24
Ingram	2022-07-21
Wakefield Co	2022-07-20
Reiter’s Scientific & Pro Books	2022-07-19
Pacific Bell	2022-07-15
Suburban Propane	2022-07-15
Data Reproductions Corp	2022-07-10
Abbey Office Furnishings	2022-07-05
Cahners Publishing Company	2022-06-30

Alternative: Use Subquery in SELECT

This is a second solution to the same problem statement:

“We want a query that:

• Gets the names of all vendors.
• Finds the latest invoice date for each vendor.
• Lists the vendors with the most recent invoices at the top.”

SELECT vendor_name,
    (SELECT MAX(invoice_date) 
     FROM invoices
     WHERE vendor_id = vendors.vendor_id) AS latest_inv
FROM vendors
ORDER BY latest_inv DESC

Displaying records 1 - 15

vendor_name	latest_inv
Federal Express Corporation	2022-08-02
Blue Cross	2022-08-01
Malloy Lithographing Inc	2022-07-31
Cardinal Business Media, Inc.	2022-07-28
Zylka Design	2022-07-25
Ford Motor Credit Company	2022-07-24
United Parcel Service	2022-07-24
Ingram	2022-07-21
Wakefield Co	2022-07-20
Reiter’s Scientific & Pro Books	2022-07-19
Pacific Bell	2022-07-15
Suburban Propane	2022-07-15
Data Reproductions Corp	2022-07-10
Abbey Office Furnishings	2022-07-05
Cahners Publishing Company	2022-06-30

Another Alternative: Use CTE

Now, we have learned common table expression and let’s see another implementation of this query. First, let’s see all invoices of their latest invoice date

SELECT vendor_id, MAX(invoice_date) 
FROM invoices
GROUP BY vendor_id

Displaying records 1 - 15

vendor_id	MAX(invoice_date)
34	2022-06-09
37	2022-08-01
48	2022-05-03
72	2022-07-10
80	2022-07-28
81	2022-06-21
82	2022-07-19
83	2022-07-21
86	2022-06-11
88	2022-05-15
89	2022-04-24
90	2022-07-20
94	2022-07-05
95	2022-07-15
96	2022-04-26

Second, we can create a common table expression for the aggregated result above and use join mechanism to bring vendor name and other information from vendor table

WITH t AS 
(SELECT vendor_id, MAX(invoice_date) AS latest_inv
FROM invoices
GROUP BY vendor_id)

SELECT vendor_name, latest_inv 
FROM vendors LEFT JOIN t ON t.vendor_id = vendors.vendor_id
ORDER BY latest_inv DESC

Displaying records 1 - 15

vendor_name	latest_inv
Federal Express Corporation	2022-08-02
Blue Cross	2022-08-01
Malloy Lithographing Inc	2022-07-31
Cardinal Business Media, Inc.	2022-07-28
Zylka Design	2022-07-25
Ford Motor Credit Company	2022-07-24
United Parcel Service	2022-07-24
Ingram	2022-07-21
Wakefield Co	2022-07-20
Reiter’s Scientific & Pro Books	2022-07-19
Pacific Bell	2022-07-15
Suburban Propane	2022-07-15
Data Reproductions Corp	2022-07-10
Abbey Office Furnishings	2022-07-05
Cahners Publishing Company	2022-06-30

4.4 Write Complex Queries

1. State the problem to be solved by the query in plain language.
2. Use pseudocode to outline the query.
3. Code the subqueries and test them to be sure that they return the correct data.
4. Code and test the final query.

SELECT t1.vendor_state, vendor_name, t1.sum_of_invoices
FROM
(
    -- sum of invoice totals by vendor
    SELECT vendor_state, vendor_name,
        SUM(invoice_total) AS sum_of_invoices
    FROM vendors v JOIN invoices i 
        ON v.vendor_id = i.vendor_id
    GROUP BY vendor_state, vendor_name
) t1

JOIN
(
  -- top sum of invoice totals by state
  SELECT vendor_state,  
         MAX(sum_of_invoices)
         AS sum_of_invoices
  FROM
  (
    -- sum of invoice totals by vendor
    SELECT vendor_state, vendor_name,
        SUM(invoice_total)
        AS sum_of_invoices
    FROM vendors v JOIN invoices i 
        ON v.vendor_id = i.vendor_id
    GROUP BY vendor_state, vendor_name
  ) t2
  GROUP BY vendor_state
) t3
    ON t1.vendor_state = t3.vendor_state AND 
       t1.sum_of_invoices = t3.sum_of_invoices
ORDER BY vendor_state

10 records

vendor_state	vendor_name	sum_of_invoices
AZ	Wells Fargo Bank	662.00
CA	Digital Dreamworks	7125.34
DC	Reiter’s Scientific & Pro Books	600.00
MA	Dean Witter Reynolds	1367.50
MI	Malloy Lithographing Inc	119892.41
NV	United Parcel Service	23177.96
OH	Edward Data Services	207.78
PA	Cardinal Business Media, Inc.	265.36
TN	Federal Express Corporation	4378.02
TX	Ingram	2154.42

4.5 Logical Operators

The best way to understand logical operators is to put them into use.

ALL

• x > ALL (1, 2) <=> x > 2
• x < ALL (1, 2) <=> x < 1
• x = ALL (1, 2) <=> (x = 1) AND (x = 2)
• x != ALL (1, 2)<=> x NOT IN (1, 2)

Example: we want to get all of the invoices with invoice_totals > the largest invoice_totals from vendor with vendor_id 34.

SELECT vendor_name, invoice_number, invoice_total
FROM invoices i JOIN vendors v ON i.vendor_id = v.vendor_id
WHERE invoice_total > ALL
    (SELECT invoice_total
     FROM invoices
     WHERE vendor_id = 34)
ORDER BY vendor_name

Displaying records 1 - 15

vendor_name	invoice_number	invoice_total
Bertelsmann Industry Svcs. Inc	509786	6940.25
Cahners Publishing Company	587056	2184.50
Computerworld	367447	2433.00
Data Reproductions Corp	40318	21842.00
Dean Witter Reynolds	75C-90227	1367.50
Digital Dreamworks	P02-3772	7125.34
Franchise Tax Board	RTR-72-3662-X	1600.00
Ingram	31359783	1575.00
Malloy Lithographing Inc	0-2058	37966.19
Malloy Lithographing Inc	P-0259	26881.40
Malloy Lithographing Inc	P-0608	20551.18
Malloy Lithographing Inc	0-2060	23517.58
Malloy Lithographing Inc	0-2436	10976.06
Pollstar	77290	1750.00
United Parcel Service	989319-457	3813.33

ANY

• x > ANY (1, 2) <=> x > 1
• x < ANY (1, 2) <=> x < 2
• x = ANY (1, 2) <=> x IN (1, 2)
• x != ANY (1, 2) <=> (x != 1) OR (x != 2)

Example: we want to get all of the invoices with invoice_totals < the largest invoice_totals from vendor with vendor_id 115.

SELECT vendor_name, invoice_number, invoice_total
FROM vendors JOIN invoices
  ON vendors.vendor_id = invoices.vendor_id
WHERE invoice_total < ANY
    (SELECT invoice_total
     FROM invoices
     WHERE vendor_id = 115)

Displaying records 1 - 15

vendor_name	invoice_number	invoice_total
Federal Express Corporation	963253251	15.50
Pacific Bell	111-92R-10096	16.33
Roadway Package System, Inc	25022117	6.00
Compuserve	21-4748363	9.95
Federal Express Corporation	4-321-2596	10.00
Roadway Package System, Inc	24863706	6.00
Compuserve	21-4923721	9.95
Federal Express Corporation	4-342-8069	10.00
Pacific Bell	111-92R-10094	19.67
Pacific Bell	111-92R-10097	16.33
Federal Express Corporation	4-314-3057	13.75
Federal Express Corporation	963253267	23.50
Roadway Package System, Inc	24780512	6.00
Federal Express Corporation	43966316	10.00
Abbey Office Furnishings	203339-13	17.50

IN

If a value is in a list of values.

Subquery should return a single column (i.e. a list of values).

Example: the following returns all vendor names and their state where they have invoice total less than $100.

SELECT vendor_name, vendor_state
FROM vendors
WHERE vendor_name IN 
  ( SELECT vendor_name
    FROM invoices i INNER JOIN vendors v ON i.vendor_id = v.vendor_id
    WHERE invoice_total < 100)
ORDER BY vendor_name

10 records

vendor_name	vendor_state
Abbey Office Furnishings	CA
Cardinal Business Media, Inc.	PA
Coffee Break Service	CA
Compuserve	OH
Data Reproductions Corp	MI
Evans Executone Inc	CA
Federal Express Corporation	TN
Pacific Bell	CA
Roadway Package System, Inc	CA
Suburban Propane	CA

EXSITS

Example: The following gets all of vendors with existing invoices

SELECT vendor_id, vendor_name, vendor_state
FROM vendors
WHERE EXISTS
    (SELECT * 
     FROM invoices
     WHERE vendor_id = vendors.vendor_id)

Displaying records 1 - 15

vendor_id	vendor_name	vendor_state
34	IBM	CA
37	Blue Cross	CA
48	Fresno County Tax Collector	CA
72	Data Reproductions Corp	MI
80	Cardinal Business Media, Inc.	PA
81	Wang Laboratories, Inc.	CA
82	Reiter’s Scientific & Pro Books	DC
83	Ingram	TX
86	Computerworld	CA
88	Edward Data Services	OH
89	Evans Executone Inc	CA
90	Wakefield Co	CA
94	Abbey Office Furnishings	CA
95	Pacific Bell	CA
96	Wells Fargo Bank	AZ

To negate, i.e. to find all vendors that don’t have any invoices yet. (“NOT EXISTS”)

SELECT vendor_id, vendor_name, vendor_state
FROM vendors
WHERE NOT EXISTS
    (SELECT * 
     FROM invoices
     WHERE vendor_id = vendors.vendor_id)

Displaying records 1 - 15

vendor_id	vendor_name	vendor_state
1	US Postal Service	WI
2	National Information Data Ctr	DC
3	Register of Copyrights	DC
4	Jobtrak	CA
5	Newbrige Book Clubs	NJ
6	California Chamber Of Commerce	CA
7	Towne Advertiser’s Mailing Svcs	CA
8	BFI Industries	CA
9	Pacific Gas & Electric	CA
10	Robbins Mobile Lock And Key	CA
11	Bill Marvin Electric Inc	CA
12	City Of Fresno	CA
13	Golden Eagle Insurance Co	CA
14	Expedata Inc	CA
15	ASC Signs	CA